const db = require('../db/connMysql');

const statDao = {
    /*
     * 获取统计结果
     */
    async getStatInfo() {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                // let sql = `select
                //                count(Students.studentId) as studentCount,
                //                count(Consultations.consultationId) as consultationCount
                //            from
                //                Students,
                //                Consultations`;
                let sql = `select
                               t1.censusCount,
                               t2.warningCount,
                               t3.studentCount,
                               t4.consultationCount
                           from
                               (select count(*) as censusCount from AssessRecords) as t1,
                               (select count(*) as warningCount from Students where warningLevelId != (select warningLevelId from WarningLevels where name = '无预警')) as t2,
                               (select count(*) as studentCount from Students) as t3,
                               (select count(*) as consultationCount from Consultations) as t4
                           `;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return {
            consultationCount: result[0].consultationCount,
            studentCount: result[0].studentCount,
            warningCount: result[0].warningCount,
            censusCount: result[0].censusCount
        };
    },
    async curveInfo() {
        // 获取最近6个月咨询记录量
        let result1 = new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `SELECT
                    DATE_FORMAT(startTime, '%Y-%m') AS month,
                    COUNT(*) AS val
                FROM
                    Consultations
                WHERE
                    startTime >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
                    AND MONTH(startTime) != MONTH(DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH))
                GROUP BY
                    DATE_FORMAT(startTime, '%Y-%m')
                ORDER BY
                    month ASC;`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        // 获取最近6个月测评记录量
        let result2 = new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `SELECT
                    DATE_FORMAT(assessTime, '%Y-%m') AS month,
                    COUNT(*) AS val
                FROM
                    AssessRecords
                WHERE
                    assessTime >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
                    AND MONTH(assessTime) != MONTH(DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH))
                GROUP BY
                    DATE_FORMAT(assessTime, '%Y-%m')
                ORDER BY
                    month ASC;`;
                connection.query(sql, [], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        let result = await Promise.all([result1, result2]);
        return result;
    }
};

module.exports = statDao;